﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using EnglishLibrary;
using Helper;

namespace DataAccess
{
    //create, read, delete, update Note items
    public class NoteDAO
    {
        private string _filePath;

        public string FilePath
        {
            get { return _filePath; }
            set { _filePath = value; }
        }

        public NoteDAO(string profilePath)
        {
            _filePath = profilePath;
        }

        public NoteDAO()
        {
            _filePath = string.Empty;
        }

        #region Get Items
        public List<Note> GetListItems(bool isGetDeleted)
        {
            var list = new List<Note>();
            var query = "SELECT ITEMID, NOTETEXT, EXAMPLE, RATEID, CREATEDDATE, MODIFIEDDATE, CATEID, ISDELETED FROM NOTES";

            if (!isGetDeleted)
                query += "WHERE ISDELETED = false";

            try
            {
                DataProvider.OpenConnection(_filePath);
                var reader = DataProvider.ExecuteReader(query);

                while (reader.Read())
                {
                    var noteItem = new Note();

                    if (!reader.IsDBNull(0))
                        noteItem.ItemID = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        noteItem.NoteText = reader.GetString(1);
                    if (!reader.IsDBNull(2))
                        noteItem.Example = reader.GetString(2);
                    if (!reader.IsDBNull(3))
                        noteItem.RateID = reader.GetString(3);
                    if (!reader.IsDBNull(4))
                        noteItem.CreatedDate = reader.GetDateTime(4);
                    if (!reader.IsDBNull(5))
                        noteItem.ModifiedDate = reader.GetDateTime(5);
                    if (!reader.IsDBNull(6))
                        noteItem.CateID = reader.GetInt32(6);
                    if (!reader.IsDBNull(7))
                        noteItem.IsDeleted = reader.GetBoolean(7);

                    list.Add(noteItem);
                }
            }
            finally
            {
                DataProvider.CloseConnection();
            }
            return list;
        }
        //Get list items before date
        public List<Note> GetItemsBeforeDate(DateTime date)
        {
            var list = new List<Note>();
            var query = String.Format("SELECT ITEMID, NOTETEXT, EXAMPLE, RATEID, CREATEDDATE, MODIFIEDDATE, CATEID, ISDELETED " +
                                      "FROM NOTES " +
                                      "WHERE CREATEDATE <= #{0}#", date.ToShortDateString());

            try
            {
                DataProvider.OpenConnection(_filePath);
                var reader = DataProvider.ExecuteReader(query);

                while (reader.Read())
                {
                    var noteItem = new Note();

                    if (!reader.IsDBNull(0))
                        noteItem.ItemID = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        noteItem.NoteText = reader.GetString(1);
                    if (!reader.IsDBNull(2))
                        noteItem.Example = reader.GetString(2);
                    if (!reader.IsDBNull(3))
                        noteItem.RateID = reader.GetString(3);
                    if (!reader.IsDBNull(4))
                        noteItem.CreatedDate = reader.GetDateTime(4);
                    if (!reader.IsDBNull(5))
                        noteItem.ModifiedDate = reader.GetDateTime(5);
                    if (!reader.IsDBNull(6))
                        noteItem.CateID = reader.GetInt32(6);
                    if (!reader.IsDBNull(7))
                        noteItem.IsDeleted = reader.GetBoolean(7);

                    list.Add(noteItem);
                }
            }
            finally
            {
                DataProvider.CloseConnection();
            }
            return list;
        }
        //Get list items after date
        public List<Note> GetItemsAfterDate(DateTime date)
        {
            var list = new List<Note>();
            var query = String.Format("SELECT ITEMID, NOTETEXT, EXAMPLE, RATEID, CREATEDDATE, MODIFIEDDATE, CATEID, ISDELETED " +
                                      "FROM NOTES " +
                                      "WHERE CREATEDDATE >= #{0}#", date.ToShortDateString());

            try
            {
                DataProvider.OpenConnection(_filePath);
                var reader = DataProvider.ExecuteReader(query);

                while (reader.Read())
                {
                    var noteItem = new Note();

                    if (!reader.IsDBNull(0))
                        noteItem.ItemID = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        noteItem.NoteText = reader.GetString(1);
                    if (!reader.IsDBNull(2))
                        noteItem.Example = reader.GetString(2);
                    if (!reader.IsDBNull(3))
                        noteItem.RateID = reader.GetString(3);
                    if (!reader.IsDBNull(4))
                        noteItem.CreatedDate = reader.GetDateTime(4);
                    if (!reader.IsDBNull(5))
                        noteItem.ModifiedDate = reader.GetDateTime(5);
                    if (!reader.IsDBNull(6))
                        noteItem.CateID = reader.GetInt32(6);
                    if (!reader.IsDBNull(7))
                        noteItem.IsDeleted = reader.GetBoolean(7);

                    list.Add(noteItem);
                }
            }
            finally
            {
                DataProvider.CloseConnection();
            }
            return list;
        }
        //Get Note by ID
        public Note GetItemByID(int id)
        {
            Note noteItem = null;

            try
            {
                DataProvider.OpenConnection(_filePath);
                var query = String.Format("SELECT ITEMID, NOTETEXT, EXAMPLE, RATEID, CREATEDDATE, MODIFIEDDATE, CATEID, ISDELETED " +
                                             "FROM NOTES " +
                                             "WHERE ITEMID = {0}", id);

                var reader = DataProvider.ExecuteReader(query);

                if (reader.Read())
                {
                    noteItem = new Note();
                    if (!reader.IsDBNull(0))
                        noteItem.ItemID = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        noteItem.NoteText = reader.GetString(1);
                    if (!reader.IsDBNull(2))
                        noteItem.Example = reader.GetString(2);
                    if (!reader.IsDBNull(3))
                        noteItem.RateID = reader.GetString(3);
                    if (!reader.IsDBNull(4))
                        noteItem.CreatedDate = reader.GetDateTime(4);
                    if (!reader.IsDBNull(5))
                        noteItem.ModifiedDate = reader.GetDateTime(5);
                    if (!reader.IsDBNull(6))
                        noteItem.CateID = reader.GetInt32(6);
                    if (!reader.IsDBNull(7))
                        noteItem.IsDeleted = reader.GetBoolean(7);
                }
            }
            finally
            {
                DataProvider.CloseConnection();
            }

            return noteItem;
        }
        //Get list items by cate
        public List<Note> GetItemsByCates(List<int> catesID)
        {
            var list = new List<Note>();
            //check empty
            if (catesID.Count <= 0)
                return list;

            var query = String.Format("SELECT ITEMID, NOTETEXT, EXAMPLE, RATEID, CREATEDDATE, MODIFIEDDATE, CATEID, ISDELETED " +
                                      "FROM NOTES " +
                                      "WHERE {0}", QueryHelper.GenerateQueryStringByCateId(catesID));

            try
            {
                DataProvider.OpenConnection(_filePath);
                var reader = DataProvider.ExecuteReader(query);

                while (reader.Read())
                {
                    var noteItem = new Note();

                    if (!reader.IsDBNull(0))
                        noteItem.ItemID = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        noteItem.NoteText = reader.GetString(1);
                    if (!reader.IsDBNull(2))
                        noteItem.Example = reader.GetString(2);
                    if (!reader.IsDBNull(3))
                        noteItem.RateID = reader.GetString(3);
                    if (!reader.IsDBNull(4))
                        noteItem.CreatedDate = reader.GetDateTime(4);
                    if (!reader.IsDBNull(5))
                        noteItem.ModifiedDate = reader.GetDateTime(5);
                    if (!reader.IsDBNull(6))
                        noteItem.CateID = reader.GetInt32(6);

                    list.Add(noteItem);
                }
            }
            finally
            {
                DataProvider.CloseConnection();
            }
            return list;
        }
        #endregion

        #region Insert Items
        public bool InsertItem(Note noteItem)
        {
            //ISDELETED field is false by default
            string query = String.Format("INSERT INTO NOTES(NOTETEXT, EXAMPLE, RATEID, CREATEDDATE, MODIFIEDDATE, CATEID) " +
                                         "VALUES('{0}', '{1}', '{2}', '{3}', '{4}', {5})", 
                                         QueryHelper.Validate(noteItem.NoteText), 
                                         QueryHelper.Validate(noteItem.Example), 
                                         noteItem.RateID, 
                                         noteItem.CreatedDate, 
                                         noteItem.ModifiedDate, 
                                         noteItem.CateID);
            
            try
            {
                DataProvider.OpenConnection(_filePath);
                DataProvider.ExecuteNonquery(query);
            }
            finally
            {
                DataProvider.CloseConnection();
            }
            return true;
        }
        #endregion

        #region Update Item
        public bool UpdateItem(Note noteItem)
        {
            //TODO: [PHUC] write IsUnique or IsExist method
            var tempNote = this.GetItemByID(noteItem.ItemID);

            if (tempNote == null)
                return false;

            try
            {
                DataProvider.OpenConnection(_filePath);

                string query = String.Format("UPDATE NOTES SET NOTETEXT = \"{0}\", EXAMPLE = \"{1}\", RATEID = \"{2}\", MODIFIEDDATE = \"{3}\" " +
                                             "WHERE ITEMID = {4}",
                                             QueryHelper.Validate(noteItem.NoteText),
                                             QueryHelper.Validate(noteItem.Example),
                                             noteItem.RateID, DateTime.Now.ToShortDateString(), noteItem.ItemID);

                DataProvider.ExecuteNonquery(query);
            }
            finally
            {
                DataProvider.CloseConnection();
            }

            return true;
        }
        #endregion

        #region Delete Item
        //not truly delete, just mark it "deleted"
        public bool DeleteItem(int noteId)
        {
            //TODO: [PHUC] write IsUnique or IsExist method
            var tempNote = this.GetItemByID(noteId);

            if (tempNote == null)
                return false;

            try
            {
                DataProvider.OpenConnection(_filePath);

                string query = String.Format("UPDATE NOTES SET ISDELETED = true WHERE ITEMID = {0}", noteId);

                DataProvider.ExecuteNonquery(query);
            }
            finally
            {
                DataProvider.CloseConnection();
            }

            return true;
        }
        #endregion
    }
}
